* Firm organization with multiple establishments
* Section III.B data preparations panel

clear all 
set matsize 4000
set more off

capture log close
log using log/11_facts-p_MEorg_layer-firm_data.log, replace

use untid betnr jahr persnr beruf tentgelt w93_3_gen ao_kreis ///
	hauptbet d_educ lnw limit cens layer if jahr >= 2000 & jahr <= 2010 using data/Panel.dta, clear
desc

order limit cens lnw, after(tentgelt)
	
********************************************************************************
***	Sample restriction *********************************************************
********************************************************************************

*	Only multi-establishment firms
egen flg_estjhr = tag(betnr jahr)
bys untid jahr: egen count_est = total(flg_estjhr)
by  untid: egen ever_mbu = max(count_est)
qui keep if ever_mbu > 1
drop ever_mbu

*	Minimum firm size
bys untid jahr: egen empl_unt = count(persnr)
by  untid: egen min_empl = min(empl_unt)
qui keep if min_empl >= 10
drop min_empl

*	Cleaning: social security limit
qui replace tentgelt = limit if cens == 1
drop limit cens
qui replace lnw = ln(tentgelt) 		
count if lnw == .

********************************************************************************
***	Establishment characteristics **********************************************
********************************************************************************

bys betnr jahr: egen empl_bet = count(persnr)

merge m:1 betnr jahr using data/BHPinclUntID.dta
qui keep if _merge == 3
drop _merge

drop count_est_siab chge_est entry_unt jahr_eins_final jahr_eins_est az_vz eintritt ///
	betnr_vor untid_vor verysmall matchtype d_multest_4 ever_multest_4 always_multest_4 vorg_died ///
	mode austritt betnr_nach nachf_willbenew untid_nach vorgaenger nachfolger Sales ValueAdded ///
	erst_jahr_eintritt jahr_lzt_est first_year_ME entry_dyn exit_dyn exit_unt add_est mode_unt sector sector2 hq_sector

order hq_kreis, after(ao_bula)

********************************************************************************
***	Layer classification based on KldB1988, managerial organization ************
********************************************************************************

merge m:1 beruf using data/KldB1988_Blossfeld.dta
qui drop if _merge == 2
drop _merge

tab layer, missing
//	layer is based on KldB1988_LayerCMRHFriedrich.dta

bys untid jahr: egen lowest = min(layer)
bys untid jahr: egen second_lowest = min(layer) if layer > lowest
bys untid jahr: egen third_lowest = min(layer) if layer > second_lowest
bys untid jahr: egen highest = min(layer) if layer > third_lowest

gen layer_rank = .
replace layer_rank = 0 if layer == lowest
replace layer_rank = 1 if layer == second_lowest & layer_rank == .
replace layer_rank = 2 if layer == third_lowest & layer_rank == .
replace layer_rank = 3 if layer == highest & layer_rank == .
drop lowest *_lowest highest

//	Number of managerial layers
egen flg_untjhrlay = tag(untid jahr layer_rank)
qui replace flg_untjhrlay = 0 if layer_rank == 0
bys untid jahr: egen count_mgmt_unt = total(flg_untjhrlay)
drop flg_untjhrlay

egen flg_estjhrlay = tag(betnr jahr layer_rank)
qui replace flg_estjhrlay = 0 if layer_rank == 0
bys betnr jahr: egen count_mgmt_bet = total(flg_estjhrlay)
drop flg_estjhrlay

//	Management share (employment)
gen mgmt_lay = (layer_rank > 0)
bys betnr jahr: egen nbr_mgmt_bet = total(mgmt_lay)
bys untid jahr: egen nbr_mgmt_unt = total(mgmt_lay)
gen shr_mgmt_bet = (nbr_mgmt_bet / empl_bet) * 100
gen shr_mgmt_unt = (nbr_mgmt_unt / empl_unt) * 100
gen prdt_wkrs_unt = empl_unt - nbr_mgmt_unt
gen prdt_wkrs_bet = empl_bet - nbr_mgmt_bet
drop nbr_mgmt*

//	Management share (wage sum)
bys untid jahr: egen wage_sum_unt = total(tentgelt)
bys untid jahr: egen wage_sum_mgmt = total(mgmt_lay * tentgelt)
gen shr_mgmt_w_unt = (wage_sum_mgmt / wage_sum_unt) * 100
bys betnr jahr: egen wage_sum_bet = total(tentgelt)
bys betnr jahr: egen wage_sum_mgmt_bet = total(mgmt_lay * tentgelt)
gen shr_mgmt_w_bet = (wage_sum_mgmt_bet / wage_sum_bet) * 100

//	Management share (Blossfeld)
gen d_mgr = (blossfeld == 12)
bys betnr jahr: egen shr_mgmt_bet_bloss = mean(d_mgr * 100)
bys untid jahr: egen shr_mgmt_unt_bloss = mean(d_mgr * 100)
bys untid jahr: egen wage_unt_bloss = total(d_mgr * tentgelt)
gen shr_bloss_w_unt = (wage_unt_bloss / wage_sum_unt) * 100
bys betnr jahr: egen wage_bet_bloss = total(d_mgr * tentgelt)
gen shr_bloss_w_bet = (wage_bet_bloss / wage_sum_bet) * 100
drop wage_sum_*
drop d_mgr

cap drop flg_untjhr
egen flg_untjhr = tag(untid jahr)

********************************************************************************
***	Establishment characteristics (ctd.) ***************************************
********************************************************************************

qui keep if flg_estjhr == 1
drop flg_estjhr
drop persnr beruf tentgelt lnw layer ///
		layer_rank mgmt_lay blossfeld // Person-specific variables

egen flg_wz = tag(untid jahr w93_3_gen)
bys untid jahr: egen count_wz = total(flg_wz)
drop flg_wz

qui gen aux_hq_wz93 = w93_3_gen if hauptbet == 1
bys untid jahr: egen hq_wz93 = min(aux_hq_wz)
drop aux_hq_wz
order hq_wz93 hq_wz, after(w93_3_gen)

gen ln_prdt_workers = log(prdt_wkrs_bet)
label variable ln_prdt_workers "Log \# non-m. employees of est."

drop legal_cat

********************************************************************************
***	Firm characteristics *******************************************************
********************************************************************************

merge m:1 untid jahr using data/Amadeus_Aug2018_untid.dta
qui drop if _merge == 2
drop _merge
drop NACE Empl OperRev ValueAdded ln_sales ln_operrev

//	Geography
replace distance = 0 if hq_kreis == ao_kreis
replace distance_all = 0 if hq_kreis == ao_kreis
replace ldistance = log(distance)
replace ldistall = log(distance_all)
bys untid jahr: egen max_log_dist = max(ldistall)
bys untid jahr: egen max_dist = max(distance_all)

label variable ldistall "Log distance to HQ"
label variable max_log_dist "Maximum log distance to HQ"

preserve
qui keep if count_est > 2
keep untid jahr betnr ao_kreis
duplicates drop

merge m:1 ao_kreis using data/Middle_coordinates_max-pop.dta
drop if _merge == 2
drop _merge

gen double aux = untid * 10000
gen double untjhr = aux + jahr

fieldarea lon lat, id(untjhr) generate(area) unit(sqkm)
tabstat area, s(n mean sd min p10 q p90 max)
// Germany has 360,000 sqkm

save data/MEfirm_area_cs.dta, replace
restore

gen double aux = untid * 10000
gen double untjhr = aux + jahr
drop aux

cap drop flg_untjhr
egen flg_untjhr = tag(untid jahr)

merge m:1 untjhr using data/MEfirm_area_cs.dta
drop if _merge == 2
tab count_est if _merge == 1 & flg_untjhr == 1, m sort
drop _merge
drop untjhr

qui gen ln_area = log(area)

//	Size
gen ln_prdt_wkr_unt = log(prdt_wkrs_unt)
label variable ln_prdt_wkr_unt "Log \# non-m. employees of firm"

qui gen ln_sales = log(Sales)
label variable ln_sales "Log sales of firm"

// Legal form
qui gen legal_form = .
qui replace legal_form = 1 if (Rechtsform_neu == 8 | Rechtsform_neu == 16 | Rechtsform_neu == 17 | Rechtsform_neu == 19 | Rechtsform_neu == 29)
qui replace legal_form = 2 if (Rechtsform_neu == 3 | Rechtsform_neu == 11 | Rechtsform_neu == 12 | Rechtsform_neu == 14 | Rechtsform_neu == 15)
qui replace legal_form = 3 if (Rechtsform_neu == 13)
qui replace legal_form = 4 if (Rechtsform_neu == 5 | Rechtsform_neu == 22 | Rechtsform_neu == 23 | Rechtsform_neu == 24)
label define legal 1 "Einzelunternehmen" 2 "GmbH & coKG" 3 "GmbH" 4 "AG"
label values legal_form legal

qui tab hq_wz93, gen(d_93wz)
qui tab w93_3_gen, gen(d_est_93wz)
qui tab ao_kreis, gen(d_ao_kreis)
qui tab hq_kreis, gen(d_hq_kreis)
qui tab legal_form, gen(d_legal)

compress 
save "data/MEorg_2000-2010_p_layer-firm.dta", replace

********************************************************************************
********************************************************************************

log close
